/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

/**
 *
 * @author mona.chandrapal
 */
public class GetUserRoles extends HttpServlet {

    /**
     * Processes requests for both HTTP
     * <code>GET</code> and
     * <code>POST</code> methods.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("application/json");
        PrintWriter out = response.getWriter();

        Root root = new Root();
        ResultSet rs =null, rscount = null;
        String sql, sql1 = "";
        JSONObject corpuser;
        JSONArray tempuserlist = new JSONArray();
        JSONObject corpuserlist = new JSONObject();
        JSONArray corpuserarray;
        float numberofusers = 0;
        String pagenumber = null;
        String sortorder = null;
        String sortindex = null;
        String rows = null;
        int nrows = 0;
        String searchstring = "1";

        PreparedStatement pst= null;
        
        try {

            pagenumber = request.getParameter("page");
            sortorder = request.getParameter("sord");
            sortindex = request.getParameter("sidx");
            searchstring = request.getParameter("searchfield");

            rows = request.getParameter("rows");

            if (sortorder.equalsIgnoreCase("")) {
                sortorder = "asc";
            }
            if (sortindex.equalsIgnoreCase("")) {
                sortindex = "corpusername";
            }

            int npagenumber = Integer.parseInt(pagenumber);
            nrows = Integer.parseInt(rows);
            root.initConnection();


            //System.out.println("Searchstring: " + searchstring);
            if (searchstring == null) {
                sql = "select count(*) as count from corpusers where deleteflag =0";

            } else {
                sql = "select count(*) as count from corpusers where deleteflag =0 and corpusername like '" + searchstring + "%';";

            }



            pst = root.con.prepareStatement(sql);
            rscount = pst.executeQuery();
            rscount.next();
            numberofusers = rscount.getInt("count");
            int offset = 0;
            offset = (npagenumber - 1) * nrows;


            if (searchstring == null) {
                sql1 = "select c.id, c.corpusername, c.userrole, r.roletype from corpusers c left join roles r on c.userrole = r.roleid where c.deleteflag = 0 and r.deleteflag = 0 order by " + sortindex + " " + sortorder + "  limit " + rows + " offset ?;";

            } else {
                sql1 = "select c.id, c.corpusername, c.userrole, r.roletype from corpusers c left join roles r on c.userrole = r.roleid where c.deleteflag = 0 and r.deleteflag = 0 and corpusername like '" + searchstring + "%' order by " + sortindex + " " + sortorder + "  limit " + rows + " offset ?;";

            }


            PreparedStatement pst1 = root.con.prepareStatement(sql1);
            pst1.setInt(1, offset);

            // System.out.println(pst);
            // System.out.println(pst1);

            rs = pst1.executeQuery();
            int count = 0;

            while (rs.next()) {
                count++;
                corpuser = new JSONObject();
                corpuser.put("id", rs.getString("id"));

                corpuserarray = new JSONArray();
                //corpuserarray.add(rs.getString("id"));
                corpuserarray.add(rs.getString("corpusername"));
                corpuserarray.add(rs.getString("userrole"));
                corpuserarray.add(rs.getString("roletype"));

                corpuser.put("cell", corpuserarray);
                //System.out.println("corpuserarray: " + corpuserarray);
                tempuserlist.add(corpuser);

            }
            //System.out.println("tempuserlist: " + tempuserlist);

            float numberofpages = 0;
            numberofpages = (numberofusers / nrows);
            corpuserlist.put("total", Math.ceil(numberofpages));
            corpuserlist.put("page", npagenumber);
            corpuserlist.put("records", numberofusers);
            corpuserlist.put("rows", tempuserlist);

        } catch (Exception e) {
            System.out.println("Error reading user roles " + e);
        } finally {
			try {
				rs.close();
				pst.close();
				root.closeConnection();
			} catch (Exception e) {

			}

		}

        //System.out.println(corpuserlist.toJSONString());
        out.println(corpuserlist.toString());

    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP
     * <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Handles the HTTP
     * <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>
}
